package de.fhb.itunes.repository.DataAccess;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import de.fhb.itunes.repository.data.Film_VO;
import de.fhb.itunes.repository.data.Musik_VO;
import de.fhb.itunes.repository.jdbc.DataAccessException;
import de.fhb.itunes.repository.jdbc.JdbcDAOBase;

public class DAO_Film extends JdbcDAOBase implements IDAO_Film {

	@Override
	public Film_VO getbyID(int id) {
		Film_VO vo;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		String sqlStr = "SELECT * FROM  [MediaFileSatz] media,   [MediaFileSatz_Film] film WHERE media.id = film.id AND film.id = ? AND film.istFilm = 1";

		try {
			con = getConnection();
			ps = con.prepareStatement(sqlStr);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			vo = (Film_VO) transformSetToVO(rs);
		} catch (SQLException sqle) {
			throw new DataAccessException("Error executing " + sqlStr, sqle);
		} finally {
			try {
				// close the resources
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException sqlee) {
				throw new DataAccessException("Can't close connection", sqlee);
			}
		}
		return vo;
	}

	@Override
	public List<Film_VO> getbyName(String name) {
		List vos;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		String sqlStr = "SELECT * FROM  [MediaFileSatz] media,   [MediaFileSatz_Film] film WHERE media.id = film.id AND media.Titel = ? AND film.istFilm = 1";
		try {
			con = getConnection();
			ps = con.prepareStatement(sqlStr);
			ps.setString(1, name);
			rs = ps.executeQuery();

			vos = transformSet(rs);
		} catch (SQLException sqle) {
			throw new DataAccessException("Error executing " + sqlStr, sqle);
		} finally {
			try {
				// close the resources
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException sqlee) {
				throw new DataAccessException("Can't close connection", sqlee);
			}
		}
		return vos;
	}
	public List<Film_VO> searchByName(String name) {
		List vos;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		String sqlStr = "SELECT * FROM  [MediaFileSatz] media,   [MediaFileSatz_Film] film WHERE media.id = film.id AND media.Titel like ? AND film.istFilm = 1";
		try {
			con = getConnection();
			ps = con.prepareStatement(sqlStr);
			ps.setString(1, "%"+name+"%");
			rs = ps.executeQuery();

			vos = transformSet(rs);
		} catch (SQLException sqle) {
			throw new DataAccessException("Error executing " + sqlStr, sqle);
		} finally {
			try {
				// close the resources
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException sqlee) {
				throw new DataAccessException("Can't close connection", sqlee);
			}
		}
		return vos;
	}

	@Override
	public List<Film_VO> getAllHD() {
		List<Film_VO> vo;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		String sqlStr = "SELECT * FROM  [MediaFileSatz] media,   [MediaFileSatz_Film] film WHERE media.id = film.id AND film.istHD = 1 AND film.istFilm = 1";

		try {
			con = getConnection();
			ps = con.prepareStatement(sqlStr);
			rs = ps.executeQuery();
			vo = transformSet(rs);
		} catch (SQLException sqle) {
			throw new DataAccessException("Error executing " + sqlStr, sqle);
		} finally {
			try {
				// close the resources
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException sqlee) {
				throw new DataAccessException("Can't close connection", sqlee);
			}
		}
		return vo;
	}

	@Override
	public List<Film_VO> getAllSD() {
		List<Film_VO> vos;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		String sqlStr = "SELECT * FROM  [MediaFileSatz] media,   [MediaFileSatz_Film] film WHERE media.id = film.id AND film.istHD = 0 AND film.istFilm = 1";

		try {
			con = getConnection();
			ps = con.prepareStatement(sqlStr);
			rs = ps.executeQuery();
			vos = transformSet(rs);
		} catch (SQLException sqle) {
			throw new DataAccessException("Error executing " + sqlStr, sqle);
		} finally {
			try {
				// close the resources
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException sqlee) {
				throw new DataAccessException("Can't close connection", sqlee);
			}
		}
		return vos;
	}

	@Override
	public List<Film_VO> getLastAdded(int anzahl) {
		List<Film_VO> vos;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		String sqlStr = "SELECT TOP (?) * FROM  [MediaFileSatz] media,   [MediaFileSatz_Film] film WHERE media.id = film.id AND film.istFilm = 1 order by Added desc";

		try {
			con = getConnection();
			ps = con.prepareStatement(sqlStr);
			ps.setInt(1, anzahl);
			rs = ps.executeQuery();
			vos = transformSet(rs);
		} catch (SQLException sqle) {
			throw new DataAccessException("Error executing " + sqlStr, sqle);
		} finally {
			try {
				// close the resources
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException sqlee) {
				throw new DataAccessException("Can't close connection", sqlee);
			}
		}
		return vos;
	}

	@Override
	protected Object map2VO(ResultSet rs) throws SQLException {
		Film_VO film = new Film_VO();
		film.setAdded(rs.getDate("Added"));
		film.setGenre(rs.getString("Genre"));
		boolean ishd;
		if (rs.getByte("istHD") == 1) {
			ishd = true;
		} else {
			ishd = false;
		}
		film.setHd(ishd);
		film.setId(rs.getInt("ID"));
		film.setLaenge(rs.getLong("Länge"));
		film.setPfad(rs.getString("Pfad"));
		film.setTitel(rs.getString("Titel"));
		film.setTrackid(rs.getInt("TrackID"));
		return film;
	}

	@Override
	public List<Film_VO> getAll() {
		List<Film_VO> vos;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		String sqlStr = "SELECT * FROM  [MediaFileSatz] media,   [MediaFileSatz_Film] film WHERE media.id = film.id AND film.istFilm = 1 order by media.Titel desc";

		try {
			con = getConnection();
			ps = con.prepareStatement(sqlStr);
			rs = ps.executeQuery();
			vos = transformSet(rs);
		} catch (SQLException sqle) {
			throw new DataAccessException("Error executing " + sqlStr, sqle);
		} finally {
			try {
				// close the resources
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException sqlee) {
				throw new DataAccessException("Can't close connection", sqlee);
			}
		}
		return vos;
	}

}
